java DataBaseExecutor
package com.icss.core.db;
import com.icss.core.util.UUIDGenerator;
import com.icss.core.util.format.ValueObjectFormat;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public abstract class DataBaseExecutor
{
protected Connection m_conn = null;
public static DataBaseExecutor getExecutor(Connection conn) {
DataBaseExecutor executor = null;
int dbtype = DataBaseType.getConnectionDBType(conn);
switch (dbtype) {
case 1:
executor = new OracleDataBaseExecutor(conn); break;
case 8:
executor = new AccessDataBaseExecutor(conn); break;
case 0:
break;
default:
throw new RuntimeException("unsupported db type!");
}
return executor;
}
protected DataBaseExecutor(Connection conn) {
this.m_conn = conn;
}
public Connection getConnection() { return this.m_conn;
}
public int create(RecordSet recordSet)
throws SQLException
{
for (int i = 0; i < recordSet.size(); i++)
{
create(recordSet.get(i));
}
return recordSet.size();
}
protected int create(Record record)
throws SQLException
{
PreparedStatement pstmt = null;
try
{
fullPrimaryKeyValueWithUuid(record);
StringBuffer sb = new StringBuffer("INSERT INTO ");
sb.append(record.getEntityName()).append(" ( ");
StringBuffer placeholderBuffer = new StringBuffer();
String[] fields = record.getFields();
for (int i = 0; i < fields.length; i++)
{
if (i > 0)
{
sb.append(",");
placeholderBuffer.append(",");
}
sb.append(fields[i]);
placeholderBuffer.append("?");
}
sb.append(" ) VALUES ( ").append(placeholderBuffer).append(" )");
pstmt = this.m_conn.prepareStatement(sb.toString());
Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
setPreparedStatementParameter(pstmt, record.getFields(), 1, entryColumnTypeCode, record);
int i = pstmt.execute() ? 1 : 0;
return i;
}
catch (SQLException e)
{
throw e;
}
finally
{
try
{
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException2) {
}
}
throw localObject;
}
protected int update(Record record)
throws SQLException
{
PreparedStatement pstmt = null;
try
{
String[] fields = record.getFields();
String[] primaryKeyFields = record.getPrimaryKeyFields();
String[] fieldsExceptPK = new String[fields.length - primaryKeyFields.length];
StringBuffer sb = new StringBuffer("UPDATE ");
sb.append(record.getEntityName()).append(" SET ");
int i = 0; for (int j = 0; i < fields.length; i++)
{
String field = fields[i];
if (record.containsPrimaryKey(field))
continue;
if (j > 0)
{
sb.append(",");
}
sb.append(fields[i]);
sb.append("=?");
fieldsExceptPK[(j++)] = field;
}
sb.append(" WHERE 1=1 ");
for (int i = 0; i < primaryKeyFields.length; i++)
{
String pk = primaryKeyFields[i];
Object pkValue = record.getObjectValue(pk);
if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
{
throw new DBException("some of the key column is not evaluated, the column name is " + pk);
}
sb.append("AND ");
sb.append(pk);
sb.append("=? ");
}
pstmt = this.m_conn.prepareStatement(sb.toString());
Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
setPreparedStatementParameter(pstmt, fieldsExceptPK, 1, entryColumnTypeCode, record);
setPreparedStatementParameter(pstmt, primaryKeyFields, fieldsExceptPK.length + 1, entryColumnTypeCode, record);
int i = pstmt.executeUpdate();
return i;
}
finally
{
try
{
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject1;
}
public int update(RecordSet recordSet)
throws SQLException
{
for (int i = 0; i < recordSet.size(); i++)
{
update(recordSet.get(i));
}
return recordSet.size();
}
public int delete(RecordSet recordSet)
throws SQLException
{
for (int i = 0; i < recordSet.size(); i++)
{
delete(recordSet.get(i));
}
return recordSet.size();
}
protected int delete(Record record)
throws SQLException
{
PreparedStatement pstmt = null;
try
{
String[] primaryKeyFields = record.getPrimaryKeyFields();
StringBuffer sb = new StringBuffer("DELETE FROM ");
sb.append(record.getEntityName());
sb.append(" WHERE ");
if (primaryKeyFields.length == 0)
{
throw new DBException("you must specify one column as the primary key at least ");
}
for (int i = 0; i < primaryKeyFields.length; i++)
{
String pk = primaryKeyFields[i];
Object pkValue = record.getObjectValue(pk);
if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
{
throw new DBException("some of the key column is not evaluated, the column name is " + pk);
}
if (i > 0)
{
sb.append(" AND ");
}
sb.append(pk);
sb.append("=?");
}
pstmt = this.m_conn.prepareStatement(sb.toString());
Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
setPreparedStatementParameter(pstmt, primaryKeyFields, 1, entryColumnTypeCode, record);
int i = pstmt.executeUpdate();
return i;
}
finally
{
try
{
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject1;
}
public int delete(String entryName, String fieldName, List values)
{
return 0;
}
public int execute(String sql)
throws SQLException
{
PreparedStatement pstmt = null;
try
{
pstmt = this.m_conn.prepareStatement(sql);
int i = pstmt.executeUpdate();
return i;
}
finally
{
try
{
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
public Record findFirst(String sql)
throws SQLException
{
RecordSet recs = find(sql);
return (recs != null) && (recs.size() > 0) ? recs.get(0) : null;
}
public RecordSet find(String sql)
throws SQLException
{
RecordSet records = new RecordSet();
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = this.m_conn.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Map entryColumnType = getEntryColumnType(rsmd);
while (rs.next())
{
Record record = fillRecord(rs, rsmd, entryColumnType);
records.add(record);
}
RecordSet localRecordSet1 = records;
return localRecordSet1;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
public RecordSet find(String sql, PagingInfo pagingInfo)
throws SQLException
{
List selectColumnList = getFieldsFromSqlStatment(sql);
StringBuffer selectedFields = new StringBuffer("");
for (int i = 0; i < selectColumnList.size(); i++)
{
selectedFields.append((String)selectColumnList.get(i));
selectedFields.append(",");
}
selectedFields.setCharAt(selectedFields.length() - 1, ' ');
int iPageSize = pagingInfo.getPageSize();
int iPageNo = pagingInfo.getCurrentPageNo();
int iRecordCount = getRecordTotalCount(sql);
pagingInfo.setTotalRecordCount(iRecordCount);
StringBuffer sb = new StringBuffer();
sb.append("SELECT ").append(selectedFields).append(" FROM (");
sb.append("SELECT TBL.*,ROWNUM RN FROM (");
sb.append(sql);
sb.append(")TBL WHERE ROWNUM <= ").append(iPageNo * iPageSize);
sb.append(") WHERE RN > ").append((iPageNo - 1) * iPageSize);
return find(sb.toString());
}
private int getRecordTotalCount(String sql) throws SQLException
{
StringBuffer sb = new StringBuffer();
sb.append("SELECT COUNT(1) AS CC FROM (");
sb.append(sql);
sb.append(") ");
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
pstmt = this.m_conn.prepareStatement(sb.toString());
rs = pstmt.executeQuery();
int i = rs.next() ? rs.getInt("CC") : 0;
return i;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
protected RecordSet find(Record record, String[] columns, String[] order, PagingInfo pagingInfo) throws SQLException
{
PreparedStatement pstmt = null;
ResultSet rs = null;
RecordSet records = new RecordSet();
try
{
String[] conditionFields = record.getFields();
String sql = buildSql4FindByRecord(record, columns, conditionFields, order);
pstmt = this.m_conn.prepareStatement(sql);
Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
setPreparedStatementParameter(pstmt, conditionFields, 1, entryColumnTypeCode, record);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Map entryColumnType = getEntryColumnType(rsmd);
while (rs.next())
{
Record newRecord = fillRecord(rs, rsmd, entryColumnType);
records.add(newRecord);
}
RecordSet localRecordSet1 = records;
return localRecordSet1;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
protected RecordSet findAll(Record record, String[] columns, String[] order)
throws SQLException
{
PreparedStatement pstmt = null;
ResultSet rs = null;
RecordSet records = new RecordSet();
try
{
String sql = buildSql4FindByRecord(record, columns, null, order);
pstmt = this.m_conn.prepareStatement(sql);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Map entryColumnType = getEntryColumnType(rsmd);
while (rs.next())
{
Record newRecord = fillRecord(rs, rsmd, entryColumnType);
records.add(newRecord);
}
RecordSet localRecordSet1 = records;
return localRecordSet1;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
protected Record findByPrimaryKey(Record record, String[] selectFields)
throws SQLException
{
PreparedStatement pstmt = null;
ResultSet rs = null;
try
{
String[] primaryKeyFields = record.getPrimaryKeyFields();
String sql = buildSql4FindByRecord(record, selectFields, primaryKeyFields, null);
pstmt = this.m_conn.prepareStatement(sql);
Map entryColumnTypeCode = getEntryColumnTypeCode(record.getEntityName());
setPreparedStatementParameter(pstmt, primaryKeyFields, 1, entryColumnTypeCode, record);
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
Map entryColumnType = getEntryColumnType(rsmd);
Record ret = null;
if (rs.next())
{
ret = fillRecord(rs, rsmd, entryColumnType);
}
Record localRecord1 = ret;
return localRecord1;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (pstmt != null)
{
pstmt.close();
}
}
catch (SQLException localSQLException1) {
}
}
throw localObject;
}
protected void fullPrimaryKeyValueWithUuid(Record record)
{
Iterator pkIterator = record.primaryKeyIterator();
while (pkIterator.hasNext())
{
String pk = (String)pkIterator.next();
Object pkValue = record.getObjectValue(pk);
if ((pkValue != null) && (!"".equals(pkValue)) && (!"null".equals(pkValue)))
continue;
record.addData(pk, UUIDGenerator.getUUID());
}
}
protected void setPreparedStatementParameter(PreparedStatement pstmt, String[] fields, int baseParamIndex, Map<String, Integer> entryColumnTypeCode, Record record)
throws SQLException
{
for (int i = 0; i < fields.length; i++)
{
String columnName = fields[i];
Object columnValue = record.getObjectValue(columnName);
ValueObjectFormat formatter = ValueObjectFormat.getFormat(columnValue);
Integer columnTypeCode = (Integer)entryColumnTypeCode.get(columnName);
if (columnTypeCode == null) throw new RuntimeException("the column " + columnName + " is undefined,please check it and try again.");
setPreparedStatementParameter(pstmt, i + baseParamIndex, columnTypeCode.intValue(), columnValue, formatter);
}
}
protected Map<String, String> getEntryColumnType(String entryName) throws SQLException
{
PreparedStatement ps = null;
ResultSet rs = null;
try
{
StringBuffer sb = new StringBuffer("SELECT * FROM ").append(entryName).append(" WHERE 1=2");
ps = this.m_conn.prepareStatement(sb.toString());
rs = ps.executeQuery();
Map localMap = getEntryColumnType(rs.getMetaData());
return localMap;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (ps != null)
{
ps.close();
}
}
catch (Exception localException1) {
}
}
throw localObject;
}
protected Map<String, String> getEntryColumnType(ResultSetMetaData rsmd) throws SQLException
{
Map retMap = new HashMap();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
{
retMap.put(rsmd.getColumnName(i).toUpperCase(), rsmd.getColumnClassName(i));
}
return retMap;
}
protected Map<String, Integer> getEntryColumnTypeCode(String entryName) throws SQLException
{
PreparedStatement ps = null;
ResultSet rs = null;
try
{
StringBuffer sb = new StringBuffer("SELECT * FROM ").append(entryName).append(" WHERE 1=2");
ps = this.m_conn.prepareStatement(sb.toString());
rs = ps.executeQuery();
Map retMap = new HashMap();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++)
{
retMap.put(rsmd.getColumnName(i).toUpperCase(), Integer.valueOf(rsmd.getColumnType(i)));
}
Map localMap1 = retMap;
return localMap1;
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (ps != null)
{
ps.close();
}
}
catch (Exception localException1) {
}
}
throw localObject;
}
protected abstract void setPreparedStatementParameter(PreparedStatement paramPreparedStatement, int paramInt1, int paramInt2, Object paramObject, ValueObjectFormat paramValueObjectFormat) throws SQLException;
protected abstract Record fillRecord(ResultSet paramResultSet, ResultSetMetaData paramResultSetMetaData, Map<String, String> paramMap) throws SQLException;
private String buildSql4FindByRecord(Record record, String[] selectFields, String[] conditionFields, String[] order) {
StringBuffer sb = new StringBuffer("SELECT ");
if ((selectFields == null) || (selectFields.length == 0))
{
sb.append(" * ");
}
else
{
for (int i = 0; i < selectFields.length; i++)
{
sb.append(selectFields[i]);
sb.append(",");
}
sb.setCharAt(sb.length() - 1, ' ');
}
sb.append(" FROM ");
sb.append(record.getEntityName());
if (conditionFields != null)
{
sb.append(" WHERE 1=1 ");
if (conditionFields.length == 0)
{
throw new DBException("you do not set the search condition, at least one condition column was specified!");
}
for (int i = 0; i < conditionFields.length; i++)
{
String column = conditionFields[i];
Object pkValue = record.getObjectValue(column);
if ((pkValue == null) || ("".equals(pkValue)) || ("null".equals(pkValue)))
{
throw new DBException("some of the condition column is not evaluated, the column name is " + column);
}
sb.append("AND ");
sb.append(column);
sb.append("=? ");
}
}
if (order != null)
{
sb.append(" ORDER BY ");
for (int i = 0; i < order.length; i++)
{
sb.append(order[i]).append(",");
}
sb.setCharAt(sb.length() - 1, ' ');
}
return sb.toString();
}
private List<String> getFieldsFromSqlStatment(String sql)
{
List fields = new ArrayList();
String s = sql;
Matcher m = null;
Pattern parenthesis_pattern = Pattern.compile("\\([^\\(\\)]*\\)");
while (parenthesis_pattern.matcher(s).find())
{
s = s.replaceAll("\\([^\\(\\)]*\\)", " ");
}
Pattern select_pattern = Pattern.compile("\\s*SELECT\\s+(.*?\\s+FROM)\\s+.*", 2);
m = select_pattern.matcher(s);
if (m.find())
{
s = m.group(1);
}
Pattern fields_pattern = Pattern.compile("\\s*([^\\s\\.]*)\\s*(,|FROM)", 2);
m = fields_pattern.matcher(s);
while (m.find())
{
fields.add(m.group(1));
}
return fields;
}
}
